﻿using FastData.Caching;
using FastData.DataProvider;
using FastData.Mapping;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using System.Web.Caching;
using System.Web.Configuration;

namespace FastData
{
    public enum ExecuteType
    {
        NoneQuery = 0,
        Reader = 1,
        Scalar = 2
    }

    public class DataAccess : IDisposable
    {
        private DbConnection _connection;
        private string _connectionString;
        private List<QueryBase> _lazyQueries = new List<QueryBase>();
        private DbTransaction _transaction;

        public int CommandTimeout = 0;
        public DataProviderFactory Provider;

        protected delegate void ExecuteCommandEventHandler(string commandText);
        protected event ExecuteCommandEventHandler ExecuteCommand;

        public DataAccess(string name)
        {
            var ConnectionInfo = WebConfigurationManager.ConnectionStrings[name];
            CreateProvider(ConnectionInfo.ConnectionString, ConnectionInfo.ProviderName);
        }

        public DataAccess(string connectionString, string providerName)
        {
            CreateProvider(connectionString, providerName);
        }

        private void CreateProvider(string connectionString, string providerName)
        {
            _connectionString = connectionString;
            Provider = DataProviderFactory.GetFactory(providerName);
        }

        public string ServerVersion
        {
            get { return Connection.ServerVersion; }
        }

        public void Correct(string text1, string text2)
        {
            Provider.Correction.Add(new KeyValuePair<string, string>(text1, text2));
        }

        public DbConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = Provider.CreateConnection();
                    _connection.ConnectionString = _connectionString;
                    _connection.Open();
                }
                return _connection;
            }
        }

        public void BeginTransaction()
        {
            _transaction = Connection.BeginTransaction();
        }

        public void BeginTransaction(IsolationLevel isolationLevel)
        {
            _transaction = Connection.BeginTransaction(isolationLevel);
        }

        public void CommitTransaction()
        {
            _transaction.Commit();
            _transaction = null;
        }

        public bool RollbackTransaction()
        {
            if (_transaction != null)
            {
                _transaction.Rollback();
                _transaction = null;
                return true;
            }

            return false; ;
        }


        public void Close()
        {
            if (_connection != null)
            {
                _connection.Dispose();
                _connection = null;
            }
        }

        public void Dispose()
        {
            if (_connection != null)
            {
                _connection.Dispose();
            }
            GC.SuppressFinalize(this);
        }

        public IQueryable<T> Select<T>() where T : class,new()
        {
            return new SelectQuery<T>(this);
        }

        public InsertQuery<T> Insert<T>(T entity) where T : new()
        {
            return new InsertQuery<T>(this, entity);
        }

        public UpdateQuery<T> Update<T>(T entity) where T : new()
        {
            return new UpdateQuery<T>(this, entity);
        }

        public UpdateQuery<T> Update<T>() where T : class, new()
        {
            return Update<T>((T)null);
        }

        public DeleteQuery<T> Delete<T>(T entity) where T : new()
        {
            return new DeleteQuery<T>(this, entity);
        }

        public DeleteQuery<T> Delete<T>() where T : class, new()
        {
            return Delete<T>((T)null);
        }

        public SelectQuery Select(params string[] fields)
        {
            return new SelectQuery(this).Fields(fields);
        }

        public DeleteQuery Delete(string tableName)
        {
            return new DeleteQuery(this) { TableName = tableName };
        }

        public UpdateQuery Update(string tableName)
        {
            return new UpdateQuery(this) { TableName = tableName };
        }

        public InsertQuery Insert(string tableName)
        {
            return new InsertQuery(this) { TableName = tableName };
        }

        public Query Query(string commandText)
        {
            return new Query(this) { CommandText = commandText };
        }

        public Procedure Procedure(string name)
        {
            return new Procedure(this) { Name = name };
        }

        public Function Function(string name)
        {
            return new Function(this) { Name = name };
        }

        private DbCommand CreateCommand(QueryBase query, ExecuteType type)
        {
            DbCommand command = Provider.CreateCommand();
            command.CommandText = query.GetCommandText(type);
            command.CommandType = query.CommandType;

            int parametersCount = query.Parameters.Count;
            for (int i = 0; i < parametersCount; i++)
            {
                command.Parameters.Add(query.Parameters[i]);
            }

            BindCommand(command);

            return command;
        }

        private void BindCommand(DbCommand command)
        {
            command.Transaction = _transaction;
            command.Connection = Connection;
            command.CommandTimeout = CommandTimeout;
            if (ExecuteCommand != null)
            {
                ExecuteCommand(command.CommandText);
            }
        }

        public object GetLastIdentity()
        {
            return Query(Provider.LastIdentityText).ExecuteScalar();
        }

        internal void ExecuteLazy(QueryBase query)
        {
            _lazyQueries.Add(query);
        }

        public int ExecuteLazyQueries(IsolationLevel isolationLevel)
        {
            try
            {
                int result;

                BeginTransaction(isolationLevel);
                result = ExecuteLazyQueries();
                CommitTransaction();

                return result;
            }
            finally
            {
                RollbackTransaction();
            }
        }

        public int ExecuteLazyQueries()
        {
            try
            {
                int result = 0;
                if (Provider.SupportBatchCommand)
                {
                    result = Provider.CreateBatchQuery(_lazyQueries, ExecuteType.NoneQuery).Execute();

                    foreach (var query in _lazyQueries)
                    {
                        query.ExecuteCompleted();
                    }
                }
                else
                {
                    foreach (var query in _lazyQueries)
                    {
                        result += ExecuteNonQuery(query);
                    }
                }
                return result;
            }
            finally
            {
                _lazyQueries.Clear();
            }
        }

        public async Task<int> ExecuteLazyQueriesAsync()
        {
            try
            {
                int result = await Provider.CreateBatchQuery(_lazyQueries, ExecuteType.NoneQuery).ExecuteAsync();
                foreach (var query in _lazyQueries)
                {
                    query.ExecuteCompleted();
                }
                return result;
            }
            finally
            {
                _lazyQueries.Clear();
            }
        }

        public DataSet ExecuteLazyQueriesDataSet()
        {
            try
            {
                using (DbDataReader reader = Provider.CreateBatchQuery(_lazyQueries, ExecuteType.Reader).ExecuteReader())
                {
                    DataSet ds = new DataSet();
                    while (!reader.IsClosed)
                    {
                        ds.Tables.Add().Load(reader);
                    }
                    return ds;
                }
            }
            finally
            {
                _lazyQueries.Clear();
            }
        }

        public DataSet ExecuteLazyQueriesDataSet(TimeSpan cacheTime)
        {
            string cacheKey = string.Empty;
            for (int i = 0; i < _lazyQueries.Count; i++)
            {
                cacheKey += ((SelectQuery)_lazyQueries[i]).CacheKey;
            }

            CacheItem cacheItem = CacheProvider.Get(cacheKey);
            if (cacheItem != null)
            {
                return cacheItem.Value as DataSet;
            }

            cacheItem = new CacheItem();
            for (int i = 0; i < _lazyQueries.Count; i++)
            {
                cacheItem.Dependencies.AddRange(((SelectQuery)_lazyQueries[i]).CacheDependencies);
            }
            DataSet ds = ExecuteLazyQueriesDataSet();
            cacheItem.Value = ds;
            CacheProvider.Insert(cacheKey, cacheItem, cacheTime);
            return ds;
        }

        internal object ExecuteScalar(QueryBase query)
        {
            return FromCache(query, "Scalar", () =>
            {
                object result;
                using (DbCommand command = CreateCommand(query, ExecuteType.Scalar))
                {
                    result = command.ExecuteScalar();
                }
                query.ExecuteCompleted();
                return result;
            });
        }

        internal async Task<object> ExecuteScalarAsync(QueryBase query)
        {
            return await FromCache(query, "Scalar", async () =>
            {
                object result;
                using (DbCommand command = CreateCommand(query, ExecuteType.Scalar))
                {
                    result = await command.ExecuteScalarAsync();
                }
                query.ExecuteCompleted();
                return result;
            });
        }

        internal IEnumerable<T> Execute<T>(QueryBase query)
        {
            return FromCache(query, "List", () =>
            {
                using (var reader = ExecuteReader(query))
                {
                    int fieldCount = reader.FieldCount;

                    string[] names = new string[fieldCount];
                    for (int i = 0; i < fieldCount; i++)
                    {
                        names[i] = reader.GetName(i);
                    }

                    List<object[]> rows = new List<object[]>();
                    while (reader.Read())
                    {
                        object[] row = new object[fieldCount];
                        reader.GetValues(row);
                        rows.Add(row);
                    }

                    return DataMapper.Map<T>(rows, names);
                }
            });
        }

        internal async Task<IEnumerable<T>> ExecuteAsync<T>(QueryBase query)
        {
            return await FromCache(query, "List", async () =>
            {
                using (var reader = await ExecuteReaderAsync(query))
                {
                    int fieldCount = reader.FieldCount;

                    string[] names = new string[fieldCount];
                    for (int i = 0; i < fieldCount; i++)
                    {
                        names[i] = reader.GetName(i);
                    }

                    List<object[]> rows = new List<object[]>();
                    while (await reader.ReadAsync())
                    {
                        object[] row = new object[fieldCount];
                        reader.GetValues(row);
                        rows.Add(row);
                    }

                    return DataMapper.Map<T>(rows, names);
                }
            });
        }

        internal DbDataReader ExecuteReader(QueryBase query)
        {
            DbDataReader reader;
            using (DbCommand command = CreateCommand(query, ExecuteType.Reader))
            {
                reader = command.ExecuteReader();
            }
            query.ExecuteCompleted();
            return reader;
        }

        internal async Task<DbDataReader> ExecuteReaderAsync(QueryBase query)
        {
            DbDataReader reader;
            using (DbCommand command = CreateCommand(query, ExecuteType.Reader))
            {
                reader = await command.ExecuteReaderAsync();
            }
            query.ExecuteCompleted();
            return reader;
        }

        internal DataRow GetDataRow(DataTable dataTable)
        {
            DataRowCollection Rows = dataTable.Rows;
            if (Rows.Count == 0)
            {
                return null;
            }
            if (Rows.Count > 1)
            {
                throw new Exception("The result contains more than one element");
            }
            return Rows[0];
        }

        internal DataTable ExecuteDataTable(QueryBase query)
        {
            return FromCache(query, "DataTable", () =>
            {
                using (var reader = ExecuteReader(query))
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    return dt;
                }
            });
        }

        internal async Task<DataTable> ExecuteDataTableAsync(QueryBase query)
        {
            return await FromCache(query, "DataTable", async () =>
            {
                using (var reader = await ExecuteReaderAsync(query))
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    return dt;
                }
            });
        }

        internal int ExecuteNonQuery(QueryBase query)
        {
            int result;
            using (DbCommand command = CreateCommand(query, ExecuteType.NoneQuery))
            {
                result = command.ExecuteNonQuery();
            }
            query.ExecuteCompleted();
            return result;
        }

        internal async Task<int> ExecuteNonQueryAsync(QueryBase query)
        {
            int result;
            using (DbCommand command = CreateCommand(query, ExecuteType.NoneQuery))
            {
                result = await command.ExecuteNonQueryAsync();
            }
            query.ExecuteCompleted();
            return result;
        }

        private T FromCache<T>(QueryBase query, string key, Func<T> fromDb)
        {
            if (query.SlidingExpiration == Cache.NoSlidingExpiration && query.AbsoluteExpiration == Cache.NoAbsoluteExpiration)
            {
                return fromDb();
            }

            key += query.CacheKey + _connectionString;
            var cachedValue = CacheProvider.GetValue(key);
            if (cachedValue != null)
            {
                return (T)cachedValue;
            }

            T newValue = fromDb();
            var cacheItem = new CacheItem(newValue);
            cacheItem.Dependencies.AddRange(query.CacheDependencies);
            CacheProvider.Insert(key, cacheItem, query.AbsoluteExpiration, query.SlidingExpiration);

            return newValue;
        }
    }

}
